﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Controller;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC03BO : Sub loai tour
    /// </summary>
    public class VYC03BO : DBConnectionBO
    {
        public VYC03BO()
            : base()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        /// <summary>
        /// Lay danh sach cac du lieu trong VYC03
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC03> GetAllList()
        {
            var list = new List<ModelVYC03>();
            const string query = "SELECT [Id],[IdLoaiTour],[Ten],[UniqueId],[NgayCapNhat],[TrangThai] FROM [VYC-03]";
            var sqlCommand = new SqlCommand(query, connection);            
            var reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                var model = new ModelVYC03();

                model.Id = Convert.ToInt32(reader["Id"]);
                model.Ten = reader["Ten"].ToString();
                model.UniqueId = reader["UniqueId"].ToString();
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC03 model)
        {
            const string query = "INSERT INTO [VYC-03]([IdLoaiTour],[Ten],[UniqueId],[NgayCapNhat],[TrangThai]) VALUES(@idloaitour, @ten, @uiqueid, GETDATE(), @trangthai)";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idloaitour", SqlDbType.Int).Value = model.IdLoaiTour;
            sqlCommand.Parameters.Add("@ten", SqlDbType.NVarChar).Value = model.Ten;
            sqlCommand.Parameters.Add("@uiqueid", SqlDbType.VarChar).Value = model.UniqueId;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;

            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;                
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC03 model)
        {
            const string query = "UPDATE [VYC-03] SET IdLoaiTour = @idloaitour ,Ten = @ten ,UniqueId = @uniqueid ,NgayCapNhat = GETDATE(), TrangThai = @trangthai WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idloaitour", SqlDbType.Int).Value = model.IdLoaiTour;
            sqlCommand.Parameters.Add("@ten", SqlDbType.NVarChar).Value = model.Ten;
            sqlCommand.Parameters.Add("@uiqueid", SqlDbType.VarChar).Value = model.UniqueId;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC03 model)
        {
            const string query = "DELETE FROM [VYC-03] WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        public List<ModelVYC03> GetById(int id)
        {
            var list = new List<ModelVYC03>();
            var query = "SELECT [Id],[IdLoaiTour],[Ten],[UniqueId],[NgayCapNhat],[TrangThai] FROM [VYC-03] WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;            
            var reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                var model = new ModelVYC03();

                model.Id = Convert.ToInt32(reader["Id"]);
                model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                model.Ten = reader["Ten"].ToString();
                model.UniqueId = reader["UniqueId"].ToString();
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Get by id loai tour
        /// </summary>
        /// <param name="idLoaitour"></param>
        /// <returns></returns>
        public List<ModelVYC03> GetByIdLoaiTour(int idLoaitour)
        {
            var list = new List<ModelVYC03>();
            const string query = "SELECT [Id],[IdLoaiTour],[Ten],[UniqueId],[NgayCapNhat],[TrangThai] FROM [VYC-03] WHERE IdLoaiTour = @idloaitour";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idloaitour", SqlDbType.Int).Value = idLoaitour;
            var reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                var model = new ModelVYC03();

                model.Id = Convert.ToInt32(reader["Id"]);
                model.Ten = reader["Ten"].ToString();
                model.UniqueId = reader["UniqueId"].ToString();
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                list.Add(model);
            }
            return list;
        }

        public List<ModelVYC03> GetByUniqueId(string uniqueid)
        {
            var list = new List<ModelVYC03>();
            var query = "SELECT [Id],[IdLoaiTour],[Ten],[UniqueId],[NgayCapNhat],[TrangThai] FROM [VYC-03] WHERE UniqueId like @uniqueid";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@uniqueid", SqlDbType.VarChar).Value = uniqueid;
            var reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                var model = new ModelVYC03();

                model.Id = Convert.ToInt32(reader["Id"]);
                model.IdLoaiTour = Convert.ToInt32(reader["IdLoaiTour"]);
                model.Ten = reader["Ten"].ToString();
                model.UniqueId = reader["UniqueId"].ToString();
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                list.Add(model);
            }
            return list;
        }

    }
}